﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Runtime.Remoting.Metadata.W3cXsd2001;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using ReportSystem_XY.Models;
using ReportSystem_XY.Utility;

namespace ReportSystem_XY.SIP
{
    public class SIPTools
    {

        public static readonly int SIPSterTime = Convert.ToInt32(ConfigurationManager.AppSettings["SIPTime"]);
        /// <summary>
        /// 校准灭菌的开始和结束时间，保证时间差为设定的值
        /// </summary>
        /// <param name="Source"></param>
        /// <param name="SQLTime"></param>
        /// <param name="BatchName"></param>
        public static void CheckSIPStepInfo(string Source, String SQLTime, string BatchName)
        {
            string checkStepString = string.Concat(new string[]
          {
                "Select * From ",
                Source,
                " Where logtime in (Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "' order by logtime"
          });
            //获取1秒间隔的数据
            DataTable data = new DataTable();
            data = SQLHelper.ExecuteDataTable(checkStepString);

            //获取灭菌步的开始和结束时间
            string sterStart = data.Compute("MIN(logtime)", "batchstep=1 and processStep=4").ToString();
            string sterStop = data.Compute("MAX(logtime)", "batchstep=1 and processStep=4").ToString();
            //计算开始和结束的时间间隔
            int ts = (int)Convert.ToDateTime(sterStop).Subtract(Convert.ToDateTime(sterStart)).TotalSeconds;
            if (ts < SIPSterTime)
            {
                //如果缺，那把开始时间往前挪
                string rightStart = (Convert.ToDateTime(sterStop).AddSeconds(-1 * SIPSterTime)).ToString();
                DataRow[] dr = data.Select("1=1", "logtime asc");
                int span = SIPSterTime - ts;
                for (int j = 0; j < span; j++)
                {
                    string strDate = (Convert.ToDateTime(rightStart).AddSeconds(j)).ToString();
                    for (int i = 0; i < dr.Length; i++)
                    {
                        if ((dr[i]["logtime"].ToString()) == strDate)
                        {
                            // string sqlA = "exec usp_testA '" + strDate + "'," + ToModel.GetRandomNumber(125.0, 130.0, 1).ToString();
                            //SQLHelper.ExecuteNonQuery(sqlA);
                        }
                    }
                }
            }
            else if (ts > SIPSterTime)
            {
                string rightStart = (Convert.ToDateTime(sterStop).AddSeconds(-1 * SIPSterTime)).ToString();
                //string rightEnd = (Convert.ToDateTime(sterStart).AddSeconds(SIPSterTime)).ToString();
                DataRow[] dr = data.Select("1=1", "logtime asc");
                int span = ts - SIPSterTime;
                for (int j = 0; j < span; j++)
                {
                    //string strDate = (Convert.ToDateTime(rightEnd).AddSeconds(j + 1)).ToString();
                    string strDate = (Convert.ToDateTime(rightStart).AddSeconds(j)).ToString();
                    for (int i = 0; i < dr.Length; i++)
                    {
                        if ((dr[i]["logtime"].ToString()) == strDate)
                        {
                            //string sqlA = "exec usp_testB '" + strDate + "'" + ToModel.GetRandomNumber(125.0, 130.0, 1).ToString();
                            //SQLHelper.ExecuteNonQuery(sqlA);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 获取步骤的时间信息
        /// </summary>
        /// <param name="Source"></param>
        /// <param name="SQLTime"></param>
        /// <param name="BatchName"></param>
        /// <returns></returns>
        public static DataTable GetSIPStepInfo(string Source, String SQLTime, string BatchName)
        {
            //组装查询步序开始结束时间信息的SQL语句
            string CmdStepInfo = string.Concat(new string[]
             {
                "SELECT DISTINCT T1.processStep,T1.batchstep,MIN(T1.StartTime)AS StartTime,MIN(T1.StopTime) AS StopTime FROM" +
                "(select [batchstep],[processStep],[logtime] as StartTime,StopTime from",
                " (select  batchstep, processStep,logtime,  lead(logtime,1,logtime)Over(order by logtime)StopTime From ",
                Source,
                " Where logtime in (Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "')  as T ) AS T1 WHERE T1.batchstep <=1 GROUP BY processStep,batchStep" // order by StartTime"
                 +
                " union (select processStep=0, batchstep,min(logtime) as StartTime,max(logtime) as StopTime from ",
                 Source,
                " where reportname='",BatchName,
               "' and batchstep=2 group by batchstep)"+

" union (select processStep=0, batchstep,min(logtime) as StartTime,max(logtime) as StopTime from ",Source,
" where reportname='",BatchName,
"' and batchstep=3 group by batchstep) order by StartTime"
            });

            return SQLHelper.ExecuteDataTable(CmdStepInfo);
        }

        public static DataTable GetCIPStepInfo(string Source, String SQLTime, string BatchName)
        {
            //组装查询步序开始结束时间信息的SQL语句
            string CmdStepInfo = string.Concat(new string[]
             {
                "SELECT T1.batchstep,MIN(T1.StartTime)AS StartTime,MAX(T1.StartTime) AS StopTime FROM" +
                "(select [batchstep],[processStep],[logtime] as StartTime,StopTime from",
                " (select  batchstep, processStep,logtime,  lead(logtime,1,logtime)Over(order by logtime)StopTime From ",
                Source,
                " Where logtime in (Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "')  as T ) AS T1 GROUP BY batchStep order by StartTime"
            });

            return SQLHelper.ExecuteDataTable(CmdStepInfo);
        }
        public static DataTable GetCIPStepInfos(string Source, string BatchName)
        {
            //组装查询步序开始结束时间信息的SQL语句
            string CmdStepInfo = string.Concat(new string[]
            {
                 "SELECT  batchStep, MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                 " WHERE reportname='",BatchName,
                 "' GROUP BY batchStep ORDER BY StartTime"
                //" WHERE reportname='",BatchName,"' and batchStep>=1 and batchStep<=3 GROUP BY batchStep
                //"SELECT * FROM "+
                //"(SELECT  batchStep, processStep=-1,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                //" WHERE reportname='",BatchName,"' and batchStep>=1 and batchStep<=3 GROUP BY batchStep union "+
                //"SELECT  batchStep=4, processStep,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",
                //Source,
                //" WHERE reportname='",BatchName,"' and batchStep=1 GROUP BY processStep union "+
                //"SELECT  batchStep, processStep=-1,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                //" WHERE reportname='",BatchName,"' and batchStep=5 GROUP BY batchStep) as t ORDER BY StartTime,processStep"
            });

            return SQLHelper.ExecuteDataTable(CmdStepInfo);
        }

        public static string GetCmdTextString(string Source, string SQLTime, string SampleTime, string BatchName)
        {
            return string.Concat(new string[]
            {
                "Select * From ",
                Source,
                " Where logtime in (Select logtime From ",
                Source,
                SQLTime,
                "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                SampleTime,
                "= 0)union Select max(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                SQLTime,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                SQLTime,
                ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                 "'  order by logtime"
            });
        }
        public static string GetCmdTextString(string Source,string SampleTime, string BatchName)
        {
            string batchNameFilter = GetBatchNameFilter(BatchName);
            return string.Concat(new string[]
            {
                "Select * From ",
                Source,
                " Where logtime in (Select logtime From ",
                Source,
                batchNameFilter,
                "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                SampleTime,
                "= 0)union Select max(logtime)logtime From ",
                Source,
                batchNameFilter,
                "group by processStep union Select min(logtime)logtime From ",
                Source,
                batchNameFilter,
                "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                batchNameFilter,
                ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                Source,
                batchNameFilter,
                ")_ Where processStep<> Offset ) order by logtime"
            });
        }
        public static string GetCmdTextString(string Source, string SQLTime, string SampleTime, string BatchName,int tankid)
        {
            if(tankid==1)
            {
                return string.Concat(new string[]
                {
                    "Select logtime,batchstep,processstep,TT2001,TT2002,TT2050,TT3003,TT3004 From ",
                    Source,
                    " Where logtime in (Select logtime From ",
                    Source,
                    SQLTime,
                    "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                    SampleTime,
                    "= 0)union Select max(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select min(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                    "' order by logtime"
                });
            }
            else if (tankid == 2)
            {
                return string.Concat(new string[]
                {
                    "Select logtime,batchstep,processstep,TT2101,TT2102,TT2150,TT3106,TT3004 From ",
                    Source,
                    " Where logtime in (Select logtime From ",
                    Source,
                    SQLTime,
                    "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                    SampleTime,
                    "= 0)union Select max(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select min(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                    "' order by logtime"
                });
            }
            else if (tankid == 3)
            {
                return string.Concat(new string[]
                {
                    "Select logtime,batchstep,processstep,TT3004,TT2104,TT3101,TT3102,TT3103,TT3104,TT3105,TT3107 From ",
                    Source,
                    " Where logtime in (Select logtime From ",
                    Source,
                    SQLTime,
                    "And (DATEDIFF(s, convert(nvarchar(20), logtime, 120), convert(nvarchar(20), GETDATE(), 120)) %",
                    SampleTime,
                    "= 0)union Select max(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select min(logtime)logtime From ",
                    Source,
                    SQLTime,
                    "group by processStep union Select logtime From(select logtime, processStep, lead(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset union Select logtime From(select logtime, processStep, lag(processStep,1,processStep)Over(order by logtime)Offset from ",
                    Source,
                    SQLTime,
                    ")_ Where processStep<> Offset ) AND reportname='",BatchName,
                    "' order by logtime"
                });
            }
            else
            {
                return null;
            }                         
        }
        public static DataTable GetSIPStepInfos(string Source, string BatchName)
        {
            string sql = string.Concat(new string[]
             {
                "SELECT * FROM "+
                "(SELECT  batchStep=1, processStep,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",
                Source,
                " WHERE reportname='",BatchName,"' and batchStep=1 GROUP BY processStep union "+
                "SELECT  batchStep, processStep=-1,MIN(logtime) as StartTime,MAX(logtime) as StopTime FROM ",Source,
                " WHERE reportname='",BatchName,"' and batchStep>1 and batchStep<=3 GROUP BY batchStep) as t ORDER BY StartTime,processStep"
             });
            return SQLHelper.ExecuteDataTable(sql);
        }

        public static DataTable GetT2000TTData(string tfrom, string tto, string sampletime)
        {           
            string timeStep = ",'TIMESTEP=" + sampletime + ",1'";
            string sql = "";
            DataTable dtReturn = new DataTable();
            DataColumn newColumn = new DataColumn("logtime", System.Type.GetType("System.String"));
            newColumn.Caption = "logtime";
            newColumn.DefaultValue = string.Empty;
            dtReturn.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("TT1304", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1304";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            //
            newColumn = new DataColumn("TT1311", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT1311";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            newColumn = new DataColumn("TT2301", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2301";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT2302", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2302";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            newColumn = new DataColumn("TT2303", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2303";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            newColumn = new DataColumn("TT2305", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2305";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            newColumn = new DataColumn("TT2307", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2307";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            newColumn = new DataColumn("TT2308", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2308";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            newColumn = new DataColumn("TT2309", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2309";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            newColumn = new DataColumn("TT2310", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2310";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);
            newColumn = new DataColumn("TT2311", System.Type.GetType("System.Double"));
            newColumn.Caption = "TT2311";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);


            sql = "TAG:R,92" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt1304 = GetArchiveTable(sql, "TT1304");

            sql = "TAG:R,93" + ",'" + tfrom + "','" + tto + "'," + timeStep;
            DataTable dt1311 = GetArchiveTable(sql, "TT1311");

            sql = "TAG:R,94" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2301 = GetArchiveTable(sql, "TT2301");

            sql = "TAG:R,95" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2302 = GetArchiveTable(sql, "TT2302");

            sql = "TAG:R,96" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2303 = GetArchiveTable(sql, "TT2303");
            sql = "TAG:R,97" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2305 = GetArchiveTable(sql, "TT2305");

            sql = "TAG:R,98" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2307 = GetArchiveTable(sql, "TT2307");
            sql = "TAG:R,99" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2308 = GetArchiveTable(sql, "TT2308");
            sql = "TAG:R,100" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2309 = GetArchiveTable(sql, "TT2309");
            sql = "TAG:R,101" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2310 = GetArchiveTable(sql, "TT2310");
            sql = "TAG:R,102" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt2311 = GetArchiveTable(sql, "TT2311");


            var query1 = from tt1304 in dt1304.AsEnumerable()
                         join tt1311 in dt1311.AsEnumerable()
                         on tt1304.Field<DateTime>("Timestamp") equals tt1311.Field<DateTime>("Timestamp") into tt01
                         from t01 in tt01.DefaultIfEmpty()

                         join tt2001 in dt2301.AsEnumerable()
                         on tt1304.Field<DateTime>("Timestamp") equals tt2001.Field<DateTime>("Timestamp") into tt02
                         from t02 in tt02.DefaultIfEmpty()

                         join tt2002 in dt2302.AsEnumerable()
                         on tt1304.Field<DateTime>("Timestamp") equals tt2002.Field<DateTime>("Timestamp") into tt03
                         from t03 in tt03.DefaultIfEmpty()

                         join tt2003 in dt2303.AsEnumerable()
                          on tt1304.Field<DateTime>("Timestamp") equals tt2003.Field<DateTime>("Timestamp") into tt04
                         from t04 in tt04.DefaultIfEmpty()

                         join tt2005 in dt2305.AsEnumerable()
                          on tt1304.Field<DateTime>("Timestamp") equals tt2005.Field<DateTime>("Timestamp") into tt05
                         from t05 in tt05.DefaultIfEmpty()


                         join tt2007 in dt2307.AsEnumerable()
                          on tt1304.Field<DateTime>("Timestamp") equals tt2007.Field<DateTime>("Timestamp") into tt06
                         from t06 in tt06.DefaultIfEmpty()

                         join tt2008 in dt2308.AsEnumerable()
                          on tt1304.Field<DateTime>("Timestamp") equals tt2008.Field<DateTime>("Timestamp") into tt07
                         from t07 in tt07.DefaultIfEmpty()

                         join tt2009 in dt2309.AsEnumerable()
                          on tt1304.Field<DateTime>("Timestamp") equals tt2009.Field<DateTime>("Timestamp") into tt08
                         from t08 in tt08.DefaultIfEmpty()

                         join tt2010 in dt2310.AsEnumerable()
                          on tt1304.Field<DateTime>("Timestamp") equals tt2010.Field<DateTime>("Timestamp") into tt09
                         from t09 in tt09.DefaultIfEmpty()

                         join tt2011 in dt2311.AsEnumerable()
                          on tt1304.Field<DateTime>("Timestamp") equals tt2011.Field<DateTime>("Timestamp") into tt10
                         from t10 in tt10.DefaultIfEmpty()

                         select new
                         {
                             Timestamp = tt1304.Field<DateTime>("Timestamp"),
                             TT1304 = tt1304.Field<double>("TT1304"),
                             TT1311 = t01 == null ? 0.0 : t01.Field<double>("TT1311"),
                             TT2301 = t02 == null ? 0.0 : t02.Field<double>("TT2301"),
                             TT2302 = t03 == null ? 0.0 : t03.Field<double>("TT2302"),
                             TT2303 = t04 == null ? 0.0 : t04.Field<double>("TT2303"),
                             TT2305 = t05 == null ? 0.0 : t05.Field<double>("TT2305"),
                             TT2307 = t06 == null ? 0.0 : t06.Field<double>("TT2307"),
                             TT2308 = t07 == null ? 0.0 : t07.Field<double>("TT2308"),
                             TT2309 = t08 == null ? 0.0 : t08.Field<double>("TT2309"),
                             TT2310 = t09 == null ? 0.0 : t09.Field<double>("TT2310"),
                             TT2311= t10 == null ? 0.0 : t10.Field<double>("TT2311")
                         };

            dtReturn.Clear();
            foreach (var obj in query1)
            {
                dtReturn.Rows.Add(obj.Timestamp.ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"), obj.TT1304, obj.TT1311, obj.TT2301, obj.TT2302, obj.TT2303, obj.TT2305, obj.TT2307, obj.TT2308, obj.TT2309, obj.TT2310, obj.TT2311);

            }
            return dtReturn;
        }

        public static DataTable GetT2000CTData(string tfrom, string tto, string sampletime)
        {

            string timeStep = ",'TIMESTEP=" + sampletime + ",1'";
            string sql = "";
            DataTable dtReturn = new DataTable();
            DataColumn newColumn = new DataColumn("logtime", System.Type.GetType("System.String"));
            newColumn.Caption = "logtime";
            newColumn.DefaultValue = string.Empty;
            dtReturn.Columns.Add(newColumn);
            //
            newColumn = new DataColumn("CT1001", System.Type.GetType("System.Double"));
            newColumn.Caption = "CT1001";
            newColumn.DefaultValue = 0.0;
            dtReturn.Columns.Add(newColumn);

            sql = "TAG:R,14" + ",'" + tfrom + "','" + tto + "'" + timeStep;
            DataTable dt1001 = GetArchiveTable(sql, "CT1001");

            var query1 = from ct1001 in dt1001.AsEnumerable()

                         select new
                         {
                             Timestamp = ct1001.Field<DateTime>("Timestamp"),
                             CT1001 = ct1001.Field<double>("CT1001")
                         };

            dtReturn.Clear();
            foreach (var obj in query1)
            {
                dtReturn.Rows.Add(obj.Timestamp.ToLocalTime(), obj.CT1001);

            }
            return dtReturn;
        }
        public static void CalSIPStep(DataTable dt)
        {
            //步序时间的调整
            DataRow[] drStep = dt.Select("1=1", "StartTime asc");
            for (int i = 1; i < drStep.Length; i++)
            {
                string a = drStep[i][2].ToString();
                string b = drStep[i - 1][3].ToString();


                int ii = ((int)Convert.ToDateTime(drStep[i][2].ToString()).Subtract(Convert.ToDateTime(drStep[i - 1][3].ToString())).TotalSeconds);
                if (ii > 1 & (int)drStep[i][0] == 1 & (int)drStep[i][1] == 4)
                {
                    string newDate = (Convert.ToDateTime(a).AddSeconds(-1)).ToString("yyyy-MM-dd HH:mm:ss");
                    string filteDate = Convert.ToDateTime(b).ToString("yyyy-MM-dd HH:mm:ss");
                    string sqlA = "exec usp_testD '" + newDate + "','" + filteDate + "'";
                    SQLHelper.ExecuteNonQuery(sqlA);
                    Thread.Sleep(200);

                }

                if ((ii > 1 & (int)drStep[i][0] == 1 & (int)drStep[i][1] < 4) || (ii > 1 & (int)drStep[i][0] > 1))
                {
                    string newDate = (Convert.ToDateTime(b).AddSeconds(1)).ToString("yyyy-MM-dd HH:mm:ss");
                    string filteDate = Convert.ToDateTime(a).ToString("yyyy-MM-dd HH:mm:ss");
                    string sqlA = "exec usp_testD '" + newDate + "','" + filteDate + "'";
                    SQLHelper.ExecuteNonQuery(sqlA);
                }
            }
        }
        public static void CalCIPStep(DataTable dt)
        {
            DataRow[] drStep = dt.Select("1=1", "StartTime asc");
            for (int i = 1; i < drStep.Length; i++)
            {
                string a = drStep[i][1].ToString();
                string b = drStep[i - 1][2].ToString();


                int ii = ((int)Convert.ToDateTime(drStep[i][1].ToString()).Subtract(Convert.ToDateTime(drStep[i - 1][2].ToString())).TotalSeconds);
                if (ii > 1)
                {
                    string newDate = (Convert.ToDateTime(b).AddSeconds(1)).ToString("yyyy-MM-dd HH:mm:ss");
                    string filteDate = Convert.ToDateTime(a).ToString("yyyy-MM-dd HH:mm:ss");
                    string sqlA = "exec usp_testE '" + newDate + "','" + filteDate + "'";
                    SQLHelper.ExecuteNonQuery(sqlA);
                }
            }
        }
        public static string GetBatchNameFilter(string batchname)
        {
            return string.Concat(new string[]
            {
                " where reportname = '",batchname,
                "' "
            });
        }
        public static void ProductionPhaseTimeCal(string reportname)
        {
            for (int i = 1; i < 15; i++)
            {
                string sql = "select * FROM tblP" + i.ToString() + " WHERE reportname = '" + reportname + "'";
                DataTable dt = new DataTable();
                dt = SQLHelper.ExecuteDataTable(sql);
                DataRow[] dr = dt.Select("1=1", "starttime asc");
                if(dr.Length>1)
                {           
                for (int j = 0; j < dr.Length - 1; j++)
                {
                    if (Convert.ToDateTime(dr[j][4].ToString()).ToString("yyyy-MM-dd HH:mm:ss") != Convert.ToDateTime(dr[j+1][3].ToString()).ToString("yyyy-MM-dd HH:mm:ss"))
                    {
                        string sqlA = "exec usp_p" + i.ToString() + " '" + dr[j + 1][3] + "','"+ reportname + "','" + dr[j][3] +"'";
                            SQLHelper.ExecuteNonQuery(sqlA);
                    }

                }
                    Thread.Sleep(500);
                }
                
            }
        }
        public static DataTable GetProductionInfo(string reportname)
        {
            string sql = "SELECT * FROM tblP1 WHERE reportname = '" + reportname + "' ";

            for (int i = 2; i <= 14; i++)
            {
                sql = sql + "UNION SELECT * FROM tblP" + i.ToString() + " WHERE reportname = '" + reportname + "' ";
            }
            return SQLHelper.ExecuteDataTable(sql);
        }
        public static string GetCmdTextString(string Source, string SampleTime, string BatchName, string start, string stop)
        {
            string batchNameFilter = string.Concat(new string[]
            {
                " where reportname = '",BatchName,
                "' AND logtime between '",start,"' and '",stop,"'"
            });
            return string.Concat(new string[]
            {
                "Select * From ",
                Source,
                batchNameFilter,
                "And DATEDIFF(s, convert(nvarchar(20),'"+start+"', 120), convert(nvarchar(20), logtime, 120)) %'"+SampleTime+"'=0 order by logtime"
                //SampleTime,
               // "= 0 order by logtime "
            });
        }

        public static string GetArchiveDataBase()
        {
            //string sqlPath=System.Environment.MachineName + @"\WinCC";
            string sql = "select name from master..sysdatabases";
            DataTable dt = SQLHelper.ExecuteDataTable(sql);

            foreach (DataRow row in dt.Rows)
            {
                string p = row["name"].ToString();
                if (p.Trim().Substring(p.Length - 1) == "R" && p.Trim().Substring(0, 3) == "CC_")
                {
                    return p.Trim().ToString();
                }
            }
            return null;
        }

        private static DataTable GetArchiveTable(string sql, string colName)
        {
            DataTable dt = OleDbHelper.ExecuteDataTable(sql);
            dt.Columns["RealValue"].ColumnName = colName;
            return dt.DefaultView.ToTable(false, new string[] { "Timestamp", colName });
        }

    }
}
